/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */

package tablasSql;

import java.sql.*;
import otros.Configuracion;

/**
 *
 * @author FERRETERIA
 */
public class Conexion {
    
    private final static org.apache.log4j.Logger log = org.apache.log4j.Logger.getLogger(Conexion.class);
    
    Connection conexion = null;
    private String url = Configuracion.getInstance().getProperty(Configuracion.DATABASE_SERVER);    //"localhost";
    private String bd = Configuracion.getInstance().getProperty(Configuracion.DATABASE_CATALOG);    //"gestion";
    private String us = Configuracion.getInstance().getProperty(Configuracion.DATABASE_USER);       //"root";
    private String psw = Configuracion.getInstance().getProperty(Configuracion.DATABASE_PSWD);      //"mysql82";
         
    public Conexion() {
        try {      
            Class.forName("com.mysql.jdbc.Driver");  
            conexion = DriverManager.getConnection ("jdbc:mysql://" + url + "/" + bd,us,psw);
        }catch(Exception e){
            log.error(e); 
        } 
    }
    
   public Connection getConnection(){
      return conexion;
   }

   //como dice su nombre, termina la conexion a la base de datos
   public void desconectar(){
        try {
            conexion.close();
            log.debug("La conexion a la  base de datos " + bd + " a terminado");
        } catch (SQLException e) {
             log.error(e); 
        }
       
   } 
   
   public ResultSet hacerConsulta (String sql) {
       try {
            // Preparamos la consulta 
            Statement s = conexion.createStatement(); 
            return s.executeQuery (sql);
       } catch (SQLException e) {
             log.warn(e); 
       }
       return null;   
   }
   
   public void hacerConsulta2 (String sql) {
       try {
            // Preparamos la consulta 
            PreparedStatement pstm = conexion.prepareStatement(sql); 
            pstm.execute ();
            pstm.close();
       } catch (SQLException e) {
             log.warn(e); 
       }
   }
   
   public boolean existe(String sql) {
       boolean valor = false;
       try {
            // Preparamos la consulta 
            Statement s = conexion.createStatement(); 
            ResultSet res =  s.executeQuery (sql);
            if (res.next()) 
                valor = true;
       } catch (SQLException e) {
              
       }
       return valor;   
   }
   
   public int aiConsulta(String sql, String[] datos, int numString, int numFloat) {
       int id = -1;
        try {
            PreparedStatement pstm;
            pstm = conexion.prepareStatement(sql);
            for(int i=0;i<datos.length;i++){
                if (i < numString)//(i < 4)       
                    pstm.setString(i+1, datos[i]);
                else if (i < numFloat)//(i < 7)
                    if (datos[i] == null)
                        pstm.setNull(i+1, java.sql.Types.FLOAT);
                    else
                        pstm.setFloat(i+1, Float.valueOf(datos[i]));
                else
                    if (datos[i] == null)
                        pstm.setNull(i+1, java.sql.Types.INTEGER);
                    else
                        pstm.setInt(i+1, Integer.valueOf(datos[i]));
            }
            pstm.execute();
            pstm.clearParameters();
            pstm = conexion.prepareStatement("SELECT @@identity AS id");
            ResultSet res = pstm.executeQuery();
            
            if (res.next()) {
                id = res.getInt("id");
            }
            pstm.close();
        } catch (SQLException ex) {
            log.error(ex + "\n" + sql + "\n");
            for (int i = 0; i < datos.length; i++) 
                log.debug(i + ":" + datos[i] + ", ");
        }
        return id;
   }
}
